SET NOCOUNT ON
USE Northgale
go
CREATE OR ALTER PROCEDURE dynamic_search_1
       @orderid     int          = NULL,
       @status      char(1)      = NULL,
       @fromdate    date         = NULL,
       @todate      date         = NULL,
       @custid      nchar(5)     = NULL,
       @custname    nvarchar(40) = NULL,
       @city        nvarchar(25) = NULL,
       @region      nvarchar(15) = NULL,
       @prodid      int          = NULL,
       @prodname    nvarchar(40) = NULL,
       @employeestr varchar(MAX) = NULL,
       @employeetbl dbo.intlist_tbltype READONLY,
       @debug       bit          = 0 AS

DECLARE @sql        nvarchar(MAX),
        @paramlist  nvarchar(4000),
        @nl         char(2) = char(13) + char(10)

SELECT @sql =
    'SELECT o.OrderID, o.OrderDate, od.UnitPrice, od.Quantity,
            c.CustomerID, c.CustomerName, c.Address, c.City,
            c.Region, c.PostalCode, c.Country, c.Phone,
            p.ProductID, p.ProductName, p.UnitsInStock,
            p.UnitsOnOrder, o.EmployeeID
     FROM   dbo.Orders o
     JOIN   dbo.[Order Details] od ON o.OrderID = od.OrderID
     JOIN   dbo.Customers c ON o.CustomerID = c.CustomerID
     JOIN   dbo.Products p ON p.ProductID = od.ProductID
     WHERE  1 = 1' + @nl

IF @orderid IS NOT NULL
   SELECT @sql += ' AND o.OrderID = @orderid' + @nl

IF @status IS NOT NULL
   SELECT @sql += ' AND o.Status = @status' + @nl

IF @fromdate IS NOT NULL
   SELECT @sql += ' AND o.OrderDate >= @fromdate' + @nl

IF @todate IS NOT NULL
   SELECT @sql += ' AND o.OrderDate <= @todate'  + @nl

IF @custid IS NOT NULL
   SELECT @sql += ' AND o.CustomerID = @custid' + @nl

IF @custname IS NOT NULL
   SELECT @sql += ' AND c.CustomerName LIKE @custname + ''%''' + @nl

IF @city IS NOT NULL
   SELECT @sql += ' AND c.City = @city'-- + @nl

IF @region IS NOT NULL
   SELECT @sql += ' AND c.Region = @region' + @nl

IF @prodid IS NOT NULL
   SELECT @sql += 'AND od.ProductID = @prodid' + @nl

IF @prodname IS NOT NULL
   SELECT @sql += ' AND p.ProductName LIKE @prodname + ''%''' + @nl

IF @employeestr IS NOT NULL
   SELECT @sql += ' AND o.EmployeeID IN 
                   (SELECT convert(int, value) 
                     FROM string_split(@employeestr, '',''))' + @nl

IF EXISTS (SELECT * FROM @employeetbl)
   SELECT @sql += ' AND o.EmployeeID IN (SELECT val FROM @employeetbl)' + @nl

SELECT @sql += ' ORDER BY o.OrderID' + @nl

IF @debug = 1
   PRINT @sql

SELECT @paramlist = '@orderid     int,
                     @status      char(1),
                     @fromdate    date,
                     @todate      date,
                     @custid      nchar(5),
                     @custname    nvarchar(40),
                     @city        nvarchar(25),
                     @region      nvarchar(15),
                     @prodid      int,
                     @prodname    nvarchar(40),
                     @employeestr varchar(MAX),
                     @employeetbl dbo.intlist_tbltype READONLY'

EXEC sp_executesql @sql, @paramlist,
                   @orderid, @status, @fromdate, @todate,
                   @custid, @custname, @city, @region,
                   @prodid, @prodname, @employeestr, @employeetbl
go
-- Test cases. Look at the generated SQL.
EXEC dynamic_search_1 @orderid = 11000, @debug = 1

EXEC dynamic_search_1 @prodid  = 76, @custid = N'RATTC', @debug = 1

EXEC dynamic_search_1 @employeestr = '201,401,801', @region = N'OR',
                      @debug = 1

-- Bah, using @debug is for wimps only - or is it?
EXEC dynamic_search_1 @city = N'Paris', @prodid = 76--, @debug = 1
